/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package uit.ieproject.dataaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import uit.ieproject.entities.Address;
import uit.ieproject.entities.Regex;
import uit.ieproject.utilites.DbConnection;
import uit.ieproject.utilites.Info;

/**
 *
 * @author RedDevils
 */
public class AddressDAO {

    public ArrayList<Address> getAllAddress() throws SQLException {
        ArrayList<Address> addresses = new ArrayList<>();
        Connection con = DbConnection.getConnection();
        Statement stmt = con.createStatement();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM ");
        sql.append(Info.TABLE_ADDRESS);
        ResultSet rs = stmt.executeQuery(sql.toString());
        while (rs.next()) {
            Address address = new Address();
            address.setId(rs.getLong("id"));
            address.setAddress(rs.getNString("address"));
            address.setTitle(rs.getNString("title"));
            address.setParagraph(rs.getNString("paragraph"));
            address.setImage(rs.getNString("image"));
            addresses.add(address);
        }
        stmt.close();
        rs.close();
        con.close();
        return addresses;
    }

    public void addAddress(Address address) throws SQLException {
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(Info.TABLE_ADDRESS);
        sql.append(" (address, title, paragraph, image) VALUES (?,?,?,?)");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, address.getAddress());
        stmt.setString(2, address.getTitle());
        stmt.setString(3, address.getParagraph());
        stmt.setString(4, address.getImage());
        stmt.executeUpdate();
        stmt.close();
        con.close();
    }

    public void deleteAddress(long[] id) throws SQLException {
        Connection con = DbConnection.getConnection();
        con.setAutoCommit(false);
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ");
        sql.append(Info.TABLE_ADDRESS);
        sql.append(" WHERE id=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        int size = id.length;
        for (int i = 0; i < size; i++) {
            stmt.setLong(1, id[i]);
            stmt.addBatch();
        }
        stmt.executeBatch();
        con.commit();
        stmt.close();
        con.close();
    }

    public void editAddress(Address address) throws SQLException {
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE ");
        sql.append(Info.TABLE_ADDRESS);
        sql.append(" SET address=?,title=?,paragraph=?,image=? ");
        sql.append(" WHERE id=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, address.getAddress());
        stmt.setString(2, address.getTitle());
        stmt.setString(3, address.getParagraph());
        stmt.setString(4, address.getImage());
        stmt.setLong(5, address.getId());
        stmt.executeUpdate();
        stmt.close();
        con.close();
    }

    public ArrayList<String> getInfoFromAddress(String address) throws SQLException {
        ArrayList<String> infos = new ArrayList<>();
        Connection con = DbConnection.getConnection();       
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT title, paragraph, image FROM ");
        sql.append(Info.TABLE_ADDRESS);
        sql.append(" WHERE address LIKE ?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, address);
        ResultSet rs = stmt.executeQuery();
        rs.next();
        String title = rs.getNString("title");
        infos.add(title);
        String paragraph = rs.getNString("paragraph");
        infos.add(paragraph);
        String image = rs.getNString("image");
        infos.add(image);
        rs.close();
        stmt.close();
        con.close();
        return infos;
    }

    public ArrayList<String> getAddressColumn() throws SQLException {
        ArrayList<String> addresses = new ArrayList<>();
        Connection con = DbConnection.getConnection();
        Statement stmt = con.createStatement();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT address FROM ");
        sql.append(Info.TABLE_ADDRESS);
        ResultSet rs = stmt.executeQuery(sql.toString());
        while (rs.next()) {
            String strAddress = rs.getNString("address");
            addresses.add(strAddress);
        }
        rs.close();
        stmt.close();
        con.close();
        return addresses;
    }
}
